/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package junchangmis.student;

/**
 *
 * @author developers
 */
import java.util.logging.Level;
import java.util.logging.Logger;
import publicmethod.*;
import junchangmis.codecSetting.*;

import java.util.StringTokenizer;
import java.io.File;
import java.io.IOException;
import java.util.Locale;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import java.sql.*;
import java.util.Vector;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.PageOrientation;
import jxl.format.VerticalAlignment;

public class firstFeeExcel {

    private WritableCellFormat timesBoldUnderline;
    private WritableCellFormat times;
    private WritableCellFormat timesOne;
    private WritableCellFormat timesTwo;
    private WritableCellFormat timesTop;
    private WritableCellFormat timesBoldHead;
    private String inputFile;
    Vector<String> names;
    Vector<String> classCodes;
    Vector<String> sumFees;
    String range;

    public firstFeeExcel(Vector<String> names, Vector<String> classCodes, Vector<String> sumFees, String range) throws WriteException, IOException {
        this.names = names;
        this.classCodes = classCodes;
        this.sumFees = sumFees;
        this.range = range;
    }

    public void setOutputFile(String inputFile) {
        this.inputFile = inputFile;
    }

    public void write() throws IOException, WriteException {
        File file = new File(inputFile);
        WorkbookSettings wbSettings = new WorkbookSettings();

        wbSettings.setLocale(new Locale("en", "CN"));

        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
        workbook.createSheet("Report", 0);
        WritableSheet excelSheet = workbook.getSheet(0);
        //设置表格打印属性
        excelSheet.getSettings().setTopMargin(0.3);
        excelSheet.getSettings().setLeftMargin(0.1);
        excelSheet.getSettings().setRightMargin(0.1);
        excelSheet.getSettings().setBottomMargin(0.1);
        excelSheet.getSettings().setHorizontalCentre(true);
        excelSheet.getSettings().setOrientation(PageOrientation.PORTRAIT);

        createLabel(excelSheet);
        createContent(excelSheet);

        workbook.write();
        workbook.close();
    }

    private void createLabel(WritableSheet sheet)
            throws WriteException {
        WritableFont times16ptBoldHead = new WritableFont(WritableFont.TIMES, 18);
        timesBoldHead = new WritableCellFormat(times16ptBoldHead);
        timesBoldHead.setAlignment(Alignment.CENTRE);
        timesBoldHead.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesBoldHead.setWrap(true);

        WritableFont times10pt = new WritableFont(WritableFont.TIMES, 12);
        times = new WritableCellFormat(times10pt);
        times.setAlignment(Alignment.CENTRE);
        times.setVerticalAlignment(VerticalAlignment.CENTRE);
        times.setBorder(Border.ALL, BorderLineStyle.THIN);
        times.setWrap(true);

        timesOne = new WritableCellFormat(times10pt);
        timesOne.setAlignment(Alignment.CENTRE);
        timesOne.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesOne.setWrap(true);

        timesTop = new WritableCellFormat(times10pt);
        timesTop.setAlignment(Alignment.CENTRE);
        timesTop.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesTop.setWrap(true);

        timesTwo = new WritableCellFormat(times10pt);
        timesTwo.setAlignment(Alignment.LEFT);
        timesTwo.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesTwo.setBorder(Border.LEFT, BorderLineStyle.THIN);
        timesTwo.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
        timesTwo.setBorder(Border.RIGHT, BorderLineStyle.THIN);
        timesTwo.setWrap(true);


        WritableFont times10ptBoldUnderline = new WritableFont(
                WritableFont.TIMES, 12, WritableFont.BOLD, false);
        timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
        timesBoldUnderline.setAlignment(Alignment.CENTRE);
        timesBoldUnderline.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesBoldUnderline.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
        timesBoldUnderline.setWrap(true);

        CellView cv = new CellView();
        cv.setFormat(times);
        cv.setFormat(timesBoldUnderline);
        cv.setAutosize(true);

        // Write a few headers

        sheet.mergeCells(0, 0, 3, 0);
        addHeader(sheet, 0, 0, "初次交费统计表兼教职工考核专表" + "（" + range + "）");
        sheet.setRowView(0, 700);

        this.addCaption(sheet, 0, 1, "班级编码");
        this.addCaption(sheet, 1, 1, "学生姓名");
        this.addCaption(sheet, 2, 1, "交费金额");//总费用
        this.addCaption(sheet, 3, 1, "备注");
        sheet.setColumnView(0, 15);
        sheet.setColumnView(1, 12);
        sheet.setColumnView(2, 12);
        sheet.setColumnView(3, 60);
    }

    private void createContent(WritableSheet sheet) throws WriteException,
            RowsExceededException {
        int i;
        int sum = 0;
        for (i = 0; i < names.size(); i++) {
            addLabel(sheet, 0, i + 2, classCodes.get(i));
            addLabel(sheet, 1, i + 2, names.get(i));
            addLabel(sheet, 2, i + 2, sumFees.get(i));
            sum = sum + Integer.valueOf(sumFees.get(i));
            addLabel(sheet, 3, i + 2, "");
        }
        addLabelOne(sheet, 0, i + 2, "总计");
        addLabelOne(sheet, 1, i + 2, names.size() + "人");
        addLabelOne(sheet, 2, i + 2, sum + "元");
        addLabelOne(sheet, 3, i + 2, "");
        for (int j = 1; j <= i + 2; j++) {
            sheet.setRowView(j, 400);
        }
    }

    private void addHeader(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesBoldHead);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addCaption(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesBoldUnderline);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addLabelTop(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesTop);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addLabelOne(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesOne);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addLabelTwo(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesTwo);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addNumber(WritableSheet sheet, int column, int row,
            Integer integer) throws WriteException, RowsExceededException {
        Number number;
        number = new Number(column, row, integer, times);//位置 内容 格式
        sheet.addCell(number);
    }

    private void addLabel(WritableSheet sheet, int column, int row, String s)
            throws WriteException, RowsExceededException {
        Label label;
        label = new Label(column, row, s, times);
        sheet.addCell(label);
    }

    public static void main(String[] args) throws WriteException, IOException {
        newDutyTableExcel test = new newDutyTableExcel("SSY111");
        test.setOutputFile("lars.xls");
        test.write();
        System.out.println("Please check the result file under lars.xls ");
    }
}
